//----------------------------------------------------------------
//Tên Class: Cls_HoaDonNuoc
//Người thực hiện: NhatTHM
//Website: www.nhatthanh.net
//-----------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
namespace Library.Business {
	public partial class Cls_HoaDonNuoc
	{
		protected string sSQL;
		//Tham số cho Procudure
		#region Tham số cho Procudure
		protected const string st_HoaDonNuoc_Insert = "st_HoaDonNuoc_Insert";
		protected const string st_HoaDonNuoc_Update = "st_HoaDonNuoc_Update";
		protected const string st_HoaDonNuoc_Update_ALL = "st_HoaDonNuoc_Update_ALL";
		protected const string st_HoaDonNuoc_Update_Hidden = "st_HoaDonNuoc_Update_Hidden";
		protected const string st_HoaDonNuoc_Delete = "st_HoaDonNuoc_Delete";
		protected const string st_HoaDonNuoc_SelectAll = "st_HoaDonNuoc_SelectAll";
		protected const string st_HoaDonNuoc_SelectAll_Key = "st_HoaDonNuoc_SelectAll_Key";
		protected const string st_HoaDonNuoc_SelectByID = "st_HoaDonNuoc_SelectByID";
		protected const string st_HoaDonNuoc_SelectByID_Stand = "st_HoaDonNuoc_SelectByID_Stand";
		protected const string TABLE_NAME = "tbHoaDonNuoc";
		#endregion Tham số cho Procudure
		//Các tên filed của table.
		#region Các tên filed của table.
		public const string fn_ID_HoaDonNuoc = "ID_HoaDonNuoc";
		public const int len_ID_HoaDonNuoc = 4;
		public const string fn_ID_KhachHang = "ID_KhachHang";
		public const int len_ID_KhachHang = 4;
		public const string fn_ID_HeThongNuoc = "ID_HeThongNuoc";
		public const int len_ID_HeThongNuoc = 30;
		public const string fn_MaSoHoaDon = "MaSoHoaDon";
		public const int len_MaSoHoaDon = 8;
		public const string fn_Thang = "Thang";
		public const int len_Thang = 4;
		public const string fn_Nam = "Nam";
		public const int len_Nam = 4;
		public const string fn_DauKy = "DauKy";
		public const int len_DauKy = 4;
		public const string fn_CuoiKy = "CuoiKy";
		public const int len_CuoiKy = 4;
		public const string fn_SoM3TieuThu = "SoM3TieuThu";
		public const int len_SoM3TieuThu = 4;
		public const string fn_ThanhTien = "ThanhTien";
		public const int len_ThanhTien = 8;
		public const string fn_ThueGTGT = "ThueGTGT";
		public const int len_ThueGTGT = 8;
		public const string fn_TienThueGTGT = "TienThueGTGT";
		public const int len_TienThueGTGT = 8;
		public const string fn_TienThue_1 = "TienThue_1";
		public const int len_TienThue_1 = 8;
		public const string fn_TienThue_2 = "TienThue_2";
		public const int len_TienThue_2 = 8;
		public const string fn_TienPhi_1 = "TienPhi_1";
		public const int len_TienPhi_1 = 8;
		public const string fn_TienPhi_2 = "TienPhi_2";
		public const int len_TienPhi_2 = 8;
		public const string fn_TongCongThanhTien = "TongCongThanhTien";
		public const int len_TongCongThanhTien = 8;
		public const string fn_Hidden = "Hidden";
		public const int len_Hidden = 1;
		//Các filed của table.
		private int _iD_HoaDonNuoc_find;
		private int _iD_HoaDonNuoc;
		private int _iD_KhachHang;
		private string _iD_HeThongNuoc;
		private double _maSoHoaDon;
		private int _thang;
		private int _nam;
		private int _dauKy;
		private int _cuoiKy;
		private int _soM3TieuThu;
		private double _thanhTien;
		private double _thueGTGT;
		private double _tienThueGTGT;
		private double _tienThue_1;
		private double _tienThue_2;
		private double _tienPhi_1;
		private double _tienPhi_2;
		private double _tongCongThanhTien;
		private bool _hidden;
		#endregion Các tên filed của table.
		#region Các phương thức cho table.
		//Thuộc tính ID_HoaDonNuoc_find
		public int ID_HoaDonNuoc_find
		{
			get{return this._iD_HoaDonNuoc_find;}
			set{this._iD_HoaDonNuoc_find = value;}
		}
		//Thuộc tính ID_HoaDonNuoc
		public int ID_HoaDonNuoc
		{
			get{return this._iD_HoaDonNuoc;}
			set{this._iD_HoaDonNuoc = value;}
		}
		//Thuộc tính ID_KhachHang
		public int ID_KhachHang
		{
			get{return this._iD_KhachHang;}
			set{this._iD_KhachHang = value;}
		}
		//Thuộc tính ID_HeThongNuoc
		public string ID_HeThongNuoc
		{
			get{return this._iD_HeThongNuoc;}
			set{this._iD_HeThongNuoc = value;}
		}
		//Thuộc tính MaSoHoaDon
		public double MaSoHoaDon
		{
			get{return this._maSoHoaDon;}
			set{this._maSoHoaDon = value;}
		}
		//Thuộc tính Thang
		public int Thang
		{
			get{return this._thang;}
			set{this._thang = value;}
		}
		//Thuộc tính Nam
		public int Nam
		{
			get{return this._nam;}
			set{this._nam = value;}
		}
		//Thuộc tính DauKy
		public int DauKy
		{
			get{return this._dauKy;}
			set{this._dauKy = value;}
		}
		//Thuộc tính CuoiKy
		public int CuoiKy
		{
			get{return this._cuoiKy;}
			set{this._cuoiKy = value;}
		}
		//Thuộc tính SoM3TieuThu
		public int SoM3TieuThu
		{
			get{return this._soM3TieuThu;}
			set{this._soM3TieuThu = value;}
		}
		//Thuộc tính ThanhTien
		public double ThanhTien
		{
			get{return this._thanhTien;}
			set{this._thanhTien = value;}
		}
		//Thuộc tính ThueGTGT
		public double ThueGTGT
		{
			get{return this._thueGTGT;}
			set{this._thueGTGT = value;}
		}
		//Thuộc tính TienThueGTGT
		public double TienThueGTGT
		{
			get{return this._tienThueGTGT;}
			set{this._tienThueGTGT = value;}
		}
		//Thuộc tính TienThue_1
		public double TienThue_1
		{
			get{return this._tienThue_1;}
			set{this._tienThue_1 = value;}
		}
		//Thuộc tính TienThue_2
		public double TienThue_2
		{
			get{return this._tienThue_2;}
			set{this._tienThue_2 = value;}
		}
		//Thuộc tính TienPhi_1
		public double TienPhi_1
		{
			get{return this._tienPhi_1;}
			set{this._tienPhi_1 = value;}
		}
		//Thuộc tính TienPhi_2
		public double TienPhi_2
		{
			get{return this._tienPhi_2;}
			set{this._tienPhi_2 = value;}
		}
		//Thuộc tính TongCongThanhTien
		public double TongCongThanhTien
		{
			get{return this._tongCongThanhTien;}
			set{this._tongCongThanhTien = value;}
		}
		//Thuộc tính Hidden
		public bool Hidden
		{
			get{return this._hidden;}
			set{this._hidden = value;}
		}
		#endregion Các phương thức cho table.
		/// <summary>
		/// Hàm khởi tạo không có tham số.
		/// </summary>
		public Cls_HoaDonNuoc()
		{
		}

		/// <summary>
		/// Hàm khởi tạo có  tham số.
		/// </summary>
		public Cls_HoaDonNuoc(int id_khachhang, string id_hethongnuoc, double masohoadon, int thang, int nam, int dauky, int cuoiky, int som3tieuthu, double thanhtien, double thuegtgt, double tienthuegtgt, double tienthue_1, double tienthue_2, double tienphi_1, double tienphi_2, double tongcongthanhtien, bool hidden)
		{
			this._iD_KhachHang = id_khachhang;
			this._iD_HeThongNuoc = id_hethongnuoc;
			this._maSoHoaDon = masohoadon;
			this._thang = thang;
			this._nam = nam;
			this._dauKy = dauky;
			this._cuoiKy = cuoiky;
			this._soM3TieuThu = som3tieuthu;
			this._thanhTien = thanhtien;
			this._thueGTGT = thuegtgt;
			this._tienThueGTGT = tienthuegtgt;
			this._tienThue_1 = tienthue_1;
			this._tienThue_2 = tienthue_2;
			this._tienPhi_1 = tienphi_1;
			this._tienPhi_2 = tienphi_2;
			this._tongCongThanhTien = tongcongthanhtien;
			this._hidden = hidden;
		}
        /// <summary>
        /// Đếm tổng số bảng ghi.
        /// </summary>
        /// <returns></returns>
        public static int countRows(bool Hidden)
        {
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_HoaDonNuoc_Count", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
            try
            {
                con.Open();
                int intResult= (int)sqlComm.ExecuteScalar();
                return intResult;
            }
            catch (SqlException ex)
            {
                new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
                throw ex;
            }
            finally
            {
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
        }
        /// <summary>
        /// Xác định Khóa chính có tồn tại dữ liệu.
        /// </summary>
        /// <returns>Truy: Có; False: Không</returns>
        public bool Exits_Key()
        {
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_HoaDonNuoc_Count_Key", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            try
            {
                sqlComm.Parameters.Add("@ID_HoaDonNuoc", SqlDbType.Int).Value = ID_HoaDonNuoc;
                con.Open();
                if (((int)sqlComm.ExecuteScalar()) == 1) return true;
                else return false;
            }
            catch (SqlException ex)
            {
                new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
                throw ex;
                return false;
            }
            finally
            {
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
        }
        /// <summary>
        /// Xác định Khóa chính có tồn tại dữ liệu.
        /// </summary>
        /// <returns>Truy: Có; False: Không</returns>
        public static bool Exits_Key(int key_ID_HoaDonNuoc)
        {
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_HoaDonNuoc_Count_Key", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            try
            {
                sqlComm.Parameters.Add("@ID_HoaDonNuoc", SqlDbType.Int).Value = key_ID_HoaDonNuoc;
                con.Open();
                if (((int)sqlComm.ExecuteScalar()) == 1) return true;
                else return false;
            }
            catch (SqlException ex)
            {
                new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
                throw ex;
                return false;
            }
            finally
            {
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
        }
        /// <summary>
        /// Get max ID auto.
        /// </summary>
        /// <returns></returns>
        public static int getMaxIdAuto()
        {
            SqlConnection con = new AccessDB().get_Conn();
        	string sSQL = "SELECT MAX(ID_HoaDonNuoc) FROM tbHoaDonNuoc";

            SqlCommand sqlComm = new SqlCommand(sSQL, con);
            sqlComm.CommandType = CommandType.Text;
            try
            {
                con.Open();
                return (int)sqlComm.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
                throw ex;
            }
            finally
            {
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
        }
		/// <summary>
		/// Get DataTable NULL với table class.
		/// </summary>
		/// <returns></returns>
		public static DataTable getDataTable_NULL()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand("Select Top 0 * From tbHoaDonNuoc", con);
			sqlComm.CommandType = CommandType.Text;
			try
			{
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataTable dt = new DataTable();
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <returns></returns>
		public static DataTable getDataTable_My_Key()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_SelectAll_Key, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataTable dt = new DataTable();
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với Where time.
		/// </summary>
		/// <param name="strWhere">Where get ucChonNgay t</param>
		/// <returns></returns>
		public static DataTable getDataTable_Time(string strWhere)
		{
			try
			{
				string sSQL = "SELECT * FROM tbHoaDonNuoc" + strWhere;
				return getDataTable_SQL_pro(sSQL);
			}
			catch (SqlException ex)
			{
				return null;
			}
		}

		/// <summary>
		/// Get DataTable với Where.
		/// </summary>
		/// <returns></returns>
		public static DataTable getDataTable_Where(string strWhere,string FieldSort)
		{
			try
			{
			    if (FieldSort.Trim().Length == 0) FieldSort = "";
			    else FieldSort = " Order By " + FieldSort;
			    //
			    if (strWhere.Trim().Length == 0) strWhere = "";
			    else strWhere = " WHERE " + strWhere;
				string sSQL = "SELECT * FROM tbHoaDonNuoc" + strWhere + FieldSort ;
				return getDataTable_SQL_pro(sSQL);
			}
			catch (SqlException ex)
			{
				return null;
			}
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataTable getDataTable_SQL_pro(string sSQL)
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand("st_processSQL",con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			DataTable dt = new DataTable();
			try
			{
				sqlComm.Parameters.Add("@sSQL", SqlDbType.NVarChar).Value = sSQL;
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, sSQL + " - " + ex.Message);
		        throw ex;
			}
            finally
            {
                if (dt != null) dt.Dispose();
                if (sqlComm != null) sqlComm.Dispose();
                if (con != null) con.Close();
            }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataTable getDataTable_My()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_SelectAll, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		    DataTable dt = new DataTable();
			try
			{
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (dt != null) dt.Dispose();
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataSet getDataSet_My()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_SelectAll, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		    DataSet ds = new DataSet();
			try
			{
		        con.Open();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (ds != null) ds.Dispose();
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		public static DataTable getDataTable_Hidden(bool Hidden)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_HoaDonNuoc_SelectAll_Hidden", con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;

		    SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		    DataTable dt = new DataTable();
			try
			{
		        con.Open();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
				return null;
			}
		    finally
		    {
		        if (sqlDTA != null) sqlDTA.Dispose();
		        if (dt != null) dt.Dispose();
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		public static DataSet getDataSet_Hidden(bool Hidden)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_HoaDonNuoc_SelectAll_Hidden", con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;

		    SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
		    DataSet ds = new DataSet();
			try
			{
		        con.Open();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlDTA != null) sqlDTA.Dispose();
		        if (ds != null) ds.Dispose();
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataTable getDataTable_My(int key_ID_HoaDonNuoc)
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_SelectByID, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
		        con.Open();
				//
				sqlComm.Parameters.Add("@ID_HoaDonNuoc", SqlDbType.Int).Value = key_ID_HoaDonNuoc;
				//
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataTable dt = new DataTable();
				sqlDTA.Fill(dt);
				return dt;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static DataSet getDataSet_My(int key_ID_HoaDonNuoc)
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_SelectByID, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
		        con.Open();
				//
				sqlComm.Parameters.Add("@ID_HoaDonNuoc", SqlDbType.Int).Value = key_ID_HoaDonNuoc;
				//
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataSet ds = new DataSet();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

	    public static int count_With_ID_KhachHang(int ID_KhachHang)
	    {
	        SqlConnection con = new AccessDB().get_Conn();
	        SqlCommand sqlComm = new SqlCommand("st_HoaDonNuoc_CountBy_ID_KhachHang", con);
	        sqlComm.CommandType = CommandType.StoredProcedure;
	        //
	        sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = ID_KhachHang;
	        try
	        {
	            con.Open();
	            return (int)sqlComm.ExecuteScalar();
	        }
	        catch (SqlException ex)
	        {
	            new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
	            throw ex;
	        }
	        finally
	        {
	            if (sqlComm != null) sqlComm.Dispose();
	            if (con != null) con.Close();
	        }
	    }
		public static DataTable getDataTable_With_ID_KhachHang(int ID_KhachHang,bool Hidden)
		{
		    return getDataSet_With_ID_KhachHang(ID_KhachHang,Hidden).Tables[0];
		}

		public static DataSet getDataSet_With_ID_KhachHang(int ID_KhachHang,bool Hidden)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_HoaDonNuoc_SelectByID_KhachHang", con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    //
		    sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = ID_KhachHang;
		    sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
		    //
			try
			{
		        con.Open();
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataSet ds = new DataSet();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

		public static DataTable getDataTable_With_ID_KhachHang(int ID_KhachHang)
		{
		    return getDataSet_With_ID_KhachHang(ID_KhachHang).Tables[0];
		}

		public static DataSet getDataSet_With_ID_KhachHang(int ID_KhachHang)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    SqlCommand sqlComm = new SqlCommand("st_HoaDonNuoc_SelectByID_KhachHang_All",con);
		    sqlComm.CommandType = CommandType.StoredProcedure;
		    //
		    sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = ID_KhachHang;
		    //
			try
			{
		        con.Open();
				SqlDataAdapter sqlDTA = new SqlDataAdapter(sqlComm);
				DataSet ds = new DataSet();
				sqlDTA.Fill(ds);
				return ds;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				return null;
			}
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
		}

        /// <summary>
        /// Count record by LoaiTin.
        /// </summary>
        /// <param name="LoaiTin"></param>
        /// <param name="Hidden"></param>
        /// <returns></returns>
        public static int countByID_KhachHang(int ID_KhachHang,bool Hidden)
        {
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_HoaDonNuoc_CountByID_KhachHang", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            //
            sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.NVarChar).Value = ID_KhachHang;
            sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
            try
            {
                con.Open();
                return (int)sqlComm.ExecuteScalar();
            }
            catch (SqlException ex)
            {
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
            }
		    finally
		    {
		        if (sqlComm != null) sqlComm.Dispose();
		        if (con != null) con.Close();
		    }
        }
		/// <summary>
		/// Thực hiện SQL dữ liệu.
		/// </summary>
		/// <returns></returns>
		public static bool doSQL(string sSQL)
		{
            SqlConnection con = new AccessDB().get_Conn();
            SqlCommand sqlComm = new SqlCommand("st_processSQL", con);
            sqlComm.CommandType = CommandType.StoredProcedure;
            sqlComm.Parameters.Add("@sSQL", SqlDbType.NVarChar).Value = sSQL;
			try
			{
                con.Open();
				sqlComm.ExecuteNonQuery();
				return true;
			}
			catch (SqlException  ex)
			{
		        throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện Insert dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doInsert()
		{
        SqlConnection con = new AccessDB().get_Conn();
		SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_Insert,con);
        sqlComm.CommandType = CommandType.StoredProcedure;
        try
        {
				//
			    sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = ID_KhachHang;
				//ID_HeThongNuoc
				if(ID_HeThongNuoc!=null)
				    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
				else
				    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = DBNull.Value;
			    sqlComm.Parameters.Add("@MaSoHoaDon", SqlDbType.Float).Value = MaSoHoaDon;
			    sqlComm.Parameters.Add("@Thang", SqlDbType.Int).Value = Thang;
			    sqlComm.Parameters.Add("@Nam", SqlDbType.Int).Value = Nam;
			    sqlComm.Parameters.Add("@DauKy", SqlDbType.Int).Value = DauKy;
			    sqlComm.Parameters.Add("@CuoiKy", SqlDbType.Int).Value = CuoiKy;
			    sqlComm.Parameters.Add("@SoM3TieuThu", SqlDbType.Int).Value = SoM3TieuThu;
			    sqlComm.Parameters.Add("@ThanhTien", SqlDbType.Float).Value = ThanhTien;
			    sqlComm.Parameters.Add("@ThueGTGT", SqlDbType.Float).Value = ThueGTGT;
			    sqlComm.Parameters.Add("@TienThueGTGT", SqlDbType.Float).Value = TienThueGTGT;
			    sqlComm.Parameters.Add("@TienThue_1", SqlDbType.Float).Value = TienThue_1;
			    sqlComm.Parameters.Add("@TienThue_2", SqlDbType.Float).Value = TienThue_2;
			    sqlComm.Parameters.Add("@TienPhi_1", SqlDbType.Float).Value = TienPhi_1;
			    sqlComm.Parameters.Add("@TienPhi_2", SqlDbType.Float).Value = TienPhi_2;
			    sqlComm.Parameters.Add("@TongCongThanhTien", SqlDbType.Float).Value = TongCongThanhTien;
			    sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
                con.Open();
				sqlComm.ExecuteNonQuery();
                ID_HoaDonNuoc = getMaxIdAuto();
				return 1;
			}
			catch (SqlException  ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện update dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doUpdate()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_Update,con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
		        con.Open();
				//ID_HoaDonNuoc_find
				sqlComm.Parameters.Add("@ID_HoaDonNuoc_find", SqlDbType.Int).Value = ID_HoaDonNuoc_find;
			    sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = ID_KhachHang;
				//ID_HeThongNuoc
				if(ID_HeThongNuoc!=null)
				    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
				else
				    sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = DBNull.Value;
			    sqlComm.Parameters.Add("@MaSoHoaDon", SqlDbType.Float).Value = MaSoHoaDon;
			    sqlComm.Parameters.Add("@Thang", SqlDbType.Int).Value = Thang;
			    sqlComm.Parameters.Add("@Nam", SqlDbType.Int).Value = Nam;
			    sqlComm.Parameters.Add("@DauKy", SqlDbType.Int).Value = DauKy;
			    sqlComm.Parameters.Add("@CuoiKy", SqlDbType.Int).Value = CuoiKy;
			    sqlComm.Parameters.Add("@SoM3TieuThu", SqlDbType.Int).Value = SoM3TieuThu;
			    sqlComm.Parameters.Add("@ThanhTien", SqlDbType.Float).Value = ThanhTien;
			    sqlComm.Parameters.Add("@ThueGTGT", SqlDbType.Float).Value = ThueGTGT;
			    sqlComm.Parameters.Add("@TienThueGTGT", SqlDbType.Float).Value = TienThueGTGT;
			    sqlComm.Parameters.Add("@TienThue_1", SqlDbType.Float).Value = TienThue_1;
			    sqlComm.Parameters.Add("@TienThue_2", SqlDbType.Float).Value = TienThue_2;
			    sqlComm.Parameters.Add("@TienPhi_1", SqlDbType.Float).Value = TienPhi_1;
			    sqlComm.Parameters.Add("@TienPhi_2", SqlDbType.Float).Value = TienPhi_2;
			    sqlComm.Parameters.Add("@TongCongThanhTien", SqlDbType.Float).Value = TongCongThanhTien;
				int up= sqlComm.ExecuteNonQuery();
				ID_HoaDonNuoc=ID_HoaDonNuoc_find;
				return up;
			}
			catch (SqlException  ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện update dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doUpdate_ALL()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_Update_ALL,con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
		        con.Open();
				//ID_HoaDonNuoc_find
				sqlComm.Parameters.Add("@ID_HoaDonNuoc_find", SqlDbType.Int).Value = ID_HoaDonNuoc_find;
				//ID_KhachHang
				sqlComm.Parameters.Add("@ID_KhachHang", SqlDbType.Int).Value = ID_KhachHang;
				//ID_HeThongNuoc
				sqlComm.Parameters.Add("@ID_HeThongNuoc", SqlDbType.NVarChar).Value = ID_HeThongNuoc;
				//MaSoHoaDon
				sqlComm.Parameters.Add("@MaSoHoaDon", SqlDbType.Float).Value = MaSoHoaDon;
				//Thang
				sqlComm.Parameters.Add("@Thang", SqlDbType.Int).Value = Thang;
				//Nam
				sqlComm.Parameters.Add("@Nam", SqlDbType.Int).Value = Nam;
				//DauKy
				sqlComm.Parameters.Add("@DauKy", SqlDbType.Int).Value = DauKy;
				//CuoiKy
				sqlComm.Parameters.Add("@CuoiKy", SqlDbType.Int).Value = CuoiKy;
				//SoM3TieuThu
				sqlComm.Parameters.Add("@SoM3TieuThu", SqlDbType.Int).Value = SoM3TieuThu;
				//ThanhTien
				sqlComm.Parameters.Add("@ThanhTien", SqlDbType.Float).Value = ThanhTien;
				//ThueGTGT
				sqlComm.Parameters.Add("@ThueGTGT", SqlDbType.Float).Value = ThueGTGT;
				//TienThueGTGT
				sqlComm.Parameters.Add("@TienThueGTGT", SqlDbType.Float).Value = TienThueGTGT;
				//TienThue_1
				sqlComm.Parameters.Add("@TienThue_1", SqlDbType.Float).Value = TienThue_1;
				//TienThue_2
				sqlComm.Parameters.Add("@TienThue_2", SqlDbType.Float).Value = TienThue_2;
				//TienPhi_1
				sqlComm.Parameters.Add("@TienPhi_1", SqlDbType.Float).Value = TienPhi_1;
				//TienPhi_2
				sqlComm.Parameters.Add("@TienPhi_2", SqlDbType.Float).Value = TienPhi_2;
				//TongCongThanhTien
				sqlComm.Parameters.Add("@TongCongThanhTien", SqlDbType.Float).Value = TongCongThanhTien;
				//Hidden
				sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
				int up = sqlComm.ExecuteNonQuery();
				ID_HoaDonNuoc=ID_HoaDonNuoc_find;
				return up;
			}
			catch (SqlException  ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện update dữ liệu.
		/// </summary>
		/// <returns></returns>
		public static int doUpdate_KEY(string Key_ID_HoaDonNuoc_Old,string Key_ID_HoaDonNuoc_New)
		{
		    SqlConnection con = new AccessDB().get_Conn();
		    string sql = "Update tbHoaDonNuoc Set ID_HoaDonNuoc=@ID_HoaDonNuoc_New Where ID_HoaDonNuoc=@ID_HoaDonNuoc_old";
			SqlCommand sqlComm = new SqlCommand(sql,con);
			sqlComm.CommandType = CommandType.Text;
			try
			{
			    sqlComm.Parameters.Add("@ID_HoaDonNuoc_New", SqlDbType.Int).Value = Key_ID_HoaDonNuoc_New;
			    sqlComm.Parameters.Add("@ID_HoaDonNuoc_old", SqlDbType.Int).Value = Key_ID_HoaDonNuoc_Old;
		        con.Open();
		        sqlComm.ExecuteNonQuery();
		        return 1;
		    }
		    catch (SqlException  ex)
		    {
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
		        throw ex;
		    }
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện update dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doUpdateHidden()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_Update_Hidden, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
				//ID_HoaDonNuoc
				sqlComm.Parameters.Add("@ID_HoaDonNuoc", SqlDbType.Int).Value = ID_HoaDonNuoc_find;
				sqlComm.Parameters.Add("@Hidden", SqlDbType.Bit).Value = Hidden;
		        con.Open();
				sqlComm.ExecuteNonQuery();
				return 1;
			}
			catch (SqlException  ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				return -1;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}
		/// <summary>
		/// Thực hiện xóa dữ liệu.
		/// </summary>
		/// <returns></returns>
		public int doDelete()
		{
		    SqlConnection con = new AccessDB().get_Conn();
			SqlCommand sqlComm = new SqlCommand(st_HoaDonNuoc_Delete, con);
			sqlComm.CommandType = CommandType.StoredProcedure;
			try
			{
				sqlComm.Parameters.Add("@ID_HoaDonNuoc", SqlDbType.Int).Value = ID_HoaDonNuoc_find ;
				//
		        con.Open();
		    	sqlComm.ExecuteNonQuery() ;
				return 1;
			}
			catch (SqlException ex)
			{
		        new MyLog.Log_DB().setLog(TABLE_NAME, ex.Message);
				throw ex;
			}
		    finally
		    {
		        if (sqlComm != null) con.Dispose();
		        if (con != null) con.Close();
		    }
		}

		/// <summary>
		/// Conver sang Object.
		/// </summary>
		/// <returns></returns>
		public static Cls_HoaDonNuoc converDataRow_To_Object(DataRow dr)
		{
			Cls_HoaDonNuoc _Object = new Cls_HoaDonNuoc();
		    if( dr[fn_ID_HoaDonNuoc]!= DBNull.Value )
		        _Object.ID_HoaDonNuoc =  (int)dr[fn_ID_HoaDonNuoc];
		    if( dr[fn_ID_KhachHang]!= DBNull.Value )
		        _Object.ID_KhachHang =  (int)dr[fn_ID_KhachHang];
		    if( dr[fn_ID_HeThongNuoc]!= DBNull.Value )
		        _Object.ID_HeThongNuoc =  (string)dr[fn_ID_HeThongNuoc];
		    if( dr[fn_MaSoHoaDon]!= DBNull.Value )
		        _Object.MaSoHoaDon =  (double)dr[fn_MaSoHoaDon];
		    if( dr[fn_Thang]!= DBNull.Value )
		        _Object.Thang =  (int)dr[fn_Thang];
		    if( dr[fn_Nam]!= DBNull.Value )
		        _Object.Nam =  (int)dr[fn_Nam];
		    if( dr[fn_DauKy]!= DBNull.Value )
		        _Object.DauKy =  (int)dr[fn_DauKy];
		    if( dr[fn_CuoiKy]!= DBNull.Value )
		        _Object.CuoiKy =  (int)dr[fn_CuoiKy];
		    if( dr[fn_SoM3TieuThu]!= DBNull.Value )
		        _Object.SoM3TieuThu =  (int)dr[fn_SoM3TieuThu];
		    if( dr[fn_ThanhTien]!= DBNull.Value )
		        _Object.ThanhTien =  (double)dr[fn_ThanhTien];
		    if( dr[fn_ThueGTGT]!= DBNull.Value )
		        _Object.ThueGTGT =  (double)dr[fn_ThueGTGT];
		    if( dr[fn_TienThueGTGT]!= DBNull.Value )
		        _Object.TienThueGTGT =  (double)dr[fn_TienThueGTGT];
		    if( dr[fn_TienThue_1]!= DBNull.Value )
		        _Object.TienThue_1 =  (double)dr[fn_TienThue_1];
		    if( dr[fn_TienThue_2]!= DBNull.Value )
		        _Object.TienThue_2 =  (double)dr[fn_TienThue_2];
		    if( dr[fn_TienPhi_1]!= DBNull.Value )
		        _Object.TienPhi_1 =  (double)dr[fn_TienPhi_1];
		    if( dr[fn_TienPhi_2]!= DBNull.Value )
		        _Object.TienPhi_2 =  (double)dr[fn_TienPhi_2];
		    if( dr[fn_TongCongThanhTien]!= DBNull.Value )
		        _Object.TongCongThanhTien =  (double)dr[fn_TongCongThanhTien];
		    if( dr[fn_Hidden]!= DBNull.Value )
		        _Object.Hidden =  (bool)dr[fn_Hidden];
		    //============
		    return _Object;
		}

		/// <summary>
		/// Get DataTable với table class.
		/// </summary>
		/// <param name="ID">ID</param>
		/// <returns></returns>
		public static Cls_HoaDonNuoc getOject_Key(int key_ID_HoaDonNuoc)
		{
		    DataTable dt = getDataTable_My(key_ID_HoaDonNuoc);
		    //==========================
		    if (dt.Rows.Count > 0)
		        return converDataRow_To_Object(dt.Rows[0]);
		    else
		        return null;
		}

		/// <summary>
		/// Get Array object class.
		/// </summary>
		/// <returns></returns>
		public static Cls_HoaDonNuoc[] getArrayObject(DataTable dt)
		{
		    if (dt.Rows.Count == 0) return null;
		    //
		    Cls_HoaDonNuoc[] arr = new Cls_HoaDonNuoc[dt.Rows.Count];
		    int i = 0;
		    foreach (DataRow dr in dt.Rows)
		    {
		    
		        arr[i] = converDataRow_To_Object( dr  );
		        i++;
		    }
		    return arr;
		}

		/// <summary>
		/// Get Array object class.
		/// </summary>
		/// <returns></returns>
		public static Cls_HoaDonNuoc[] getArrayObject()
		{
		    return getArrayObject( getDataTable_My() );
		}

	}

}
